package com.apestech.api.condition.conlib;

import com.apestech.api.condition.ConditionTableQrySql;
import com.apestech.api.exp.XLSTable;
import com.apestech.api.query.XLSReadCallback;
import com.apestech.api.tables.SequenceManage;
import com.apestech.api.tables.modul.SeqField;
import com.apestech.formwork.condition.ConditionUtil;
import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.json.JSON;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.DBPub;
import com.apestech.framework.util.DateUtil;
import com.apestech.framework.xml.condition.modul.Condition;

import java.util.ArrayList;
import java.util.Map;

public class ConImport extends ConPub{
    private Condition condition;
    private SqlSession session;

    public ConImport(Condition condition) throws Exception {
        super(condition);
        this.condition=condition;
        session=condition.getSession();
    }

    public String importXls(String mid, String file, JSONObject json) throws Exception {
        String tabName=condition.getTableKey().getTabName(mid);
        String impTab=tabName+"_EXEC_I";
        ConditionTableQrySql sql=new ConditionTableQrySql(tabName, session);
        XLSTable xlsTable=new XLSTable();
        JSONObject sheet=new JSONObject();
        sheet.put("updateTable", impTab);
        sheet.put("index","0");
        sql.getImportInfo();
        sheet.put("head", sql.getHead());
        sheet.put("fields", sql.getListField());
        JSONObject updateField=new JSONObject();
        updateField.put("IMP_DATE", "SYSDATE");
        updateField.put("IMP_XLS_ID","{ROWID}");
        updateField.put(session.getSpecialField().getTimestamp(), "SYSDATE");
        sheet.put("updateField",updateField);
//        session.getForDDL().dropTable(impTab);
        if(!session.tableExists(impTab)){
            session.getForDDL().createImportTableFromTable(tabName+"_EXEC", impTab);
            SequenceManage sequenceManage=new SequenceManage(session);
            SeqField seqf=new SeqField();
            seqf.setTableName(impTab);
            seqf.setTableId(impTab);
            seqf.setIdField("IMP_ID");
            seqf.setPadding(12);
            sequenceManage.createSeq(seqf, true);
        }
        xlsTable.addSheet(sheet);

        final String[] idval = new String[1];

        session.importXls(xlsTable, file, new XLSReadCallback(){
            private String idvalue;
            @Override
            public JSONObject getHead(JSONObject head, String sheetName) throws Exception {
                return head;
            }
            @Override
            public JSONObject getRow(JSONObject row, int index) throws Exception {
                String sDate=null;
                String eDate=null;
                String cDate=null;
                String uDate=null;

                if(row.get("START_DATE")!=null){
                    if(row.get("START_DATE") instanceof String){
                        sDate=DateUtil.getStartDate(row.getString("START_DATE"));
                    } else {
                        sDate=DateUtil.getStartDate(DateUtil.dateToStr(row.getDate("START_DATE")));
                    }
                } else {
                    if(json.get("START_DATE")!=null){
                        sDate=DateUtil.getStartDate(json.getString("START_DATE"));
                    } else {
                        try{
                            sDate= DateUtil.getStartDate(DateUtil.dateTimeToStr(row.getDate("START_DATE")));
                        }catch(Exception e){
                            sDate=row.getString("START_DATE");
                        }
                    }
                }

                if(row.get("END_DATE")!=null){
                    if(row.get("END_DATE") instanceof String){
                        eDate=DateUtil.getEndDate(row.getString("END_DATE"));
                    } else {
                        eDate=DateUtil.getEndDate(DateUtil.dateToStr(row.getDate("END_DATE")));
                    }
                } else {
                    if(json.get("END_DATE")!=null){
                        eDate=DateUtil.getEndDate(json.getString("END_DATE"));
                    } else {
                        try{
                            eDate=DateUtil.getEndDate(DateUtil.dateTimeToStr(row.getDate("END_DATE")));
                        }catch(Exception e){
                            eDate=row.getString("END_DATE");
                        }
                    }
                }

                if (DateUtil.strToDate(sDate).after(DateUtil.strToDate(eDate))){
                    throw new Exception("时间段无效(开始日期需要小于结束日期)");
                }
                row.put("START_DATE", sDate);
                row.put("END_DATE", eDate);
                row.put("STATE","sent");

                if(json.get("CREATE_DATE")!=null){
                    cDate=DateUtil.getStartDate(json.getString("CREATE_DATE"));
                } else {
                    try{
                        cDate= DateUtil.getStartDate(DateUtil.dateTimeToStr(row.getDate("CREATE_DATE")));
                    }catch(Exception e){
                        cDate=row.getString("CREATE_DATE");
                    }
                }

                if(json.get("UPDATE_DATE")!=null){
                    uDate=DateUtil.getStartDate(json.getString("UPDATE_DATE"));
                } else {
                    try{
                        uDate= DateUtil.getStartDate(DateUtil.dateTimeToStr(row.getDate("UPDATE_DATE")));
                    }catch(Exception e){
                        uDate=row.getString("UPDATE_DATE");
                    }
                }

                row.put("CREATE_DATE", cDate);
                row.put("UPDATE_DATE", uDate);

                if(idvalue==null){
//                    DBPub.resetSql_Create(session, "EXE_CONDITION_IMPORT");
                    SequenceManage sequenceManage=new SequenceManage(session);
                    idvalue=sequenceManage.getNextNumByStr("EXE_CONDITION_IMPORT");  //impTab
                    JSONObject map=new JSONObject();
                    map.putAll(json);
                    map.putAll(row);
                    map.put("IMP_ID", idvalue);
                    map.put("CONDITION_ID", condition.getId());
                    map.put("DIMENSION_ID", mid);
                    map.put("STATE", "sent");
                    session.getForUpdate().insertTable("EXE_CONDITION_IMPORT", map);
                    idval[0] =idvalue;
                }
                row.put("IMP_ID", idvalue);
                return row;
            }
        });
        return idval[0];
    }

    public void saveImportXls(String mid, JSONObject json) throws Exception {
        String tabName = condition.getTableKey().getTabName(mid);
        String impTab = tabName + "_EXEC_I";
        JSONObject param=new JSONObject();
        param.put("IMP_ID", json.get("IMP_ID"));
        param.put("autoCompletion","0");
        param.put("STATE", "sent");
        param.put("START_DATE", DateUtil.strToDate(json.getString("START_DATE")));
        param.put("END_DATE", DateUtil.strToDate(json.getString("END_DATE")));
        JSONArray dlist= JSON.toJSONArray(json.get("ITEM_F"));
        param.put("LAST_UPDATED_STAMP_OLD", json.get("LAST_UPDATED_STAMP_OLD"));
        String sql=" UPDATE EXE_CONDITION_IMPORT SET LAST_UPDATED_STAMP=SYSDATE, START_DATE=#{START_DATE}, END_DATE=#{END_DATE} " +
                " WHERE IMP_ID=#{IMP_ID} AND TO_CHAR(LAST_UPDATED_STAMP, 'YYYYMMDD.HH24MISS')||'.000'=#{LAST_UPDATED_STAMP_OLD} "; //"AND IMP_STATE>0 ";
        if(session.getForUpdate().update(sql, param)==0){
            throw new Exception("当前数据已经改变，请重新操作！");
        }
        session.getForUpdate().update(" DELETE "+impTab+" WHERE IMP_ID=#{IMP_ID} AND IMP_STATE>'0' ", json);
        if(dlist!=null){
            int len=dlist.size();
            for(int i=0; i<len; i++){  //IMP_STATE=0
                JSONObject line=dlist.get(i);
                ArrayList<String> klist=line.getKeylist();
                int jlen=klist.size();
                for(int j=0; j<jlen; j++){
                    String key=klist.get(j);
                    Object obj=line.get(key);
                    if(obj instanceof Map){
                        JSONObject json1=JSON.toJSON(obj);
                        line.put(key, json1.getString("ID"));
                    }
                }
                line.put("IMP_STATE",0);
                session.getForUpdate().insertTable(impTab, line);
            }
        }
    }

    public void deleteImport(String mid, JSONObject json) throws Exception {
        String tabName = condition.getTableKey().getTabName(mid);
        String impTab = tabName + "_EXEC_I";
        JSONObject param=new JSONObject();
        param.put("IMP_ID", json.get("IMP_ID"));
        param.put("LAST_UPDATED_STAMP_OLD", json.get("LAST_UPDATED_STAMP_OLD"));
        param.put("autoCompletion","0");
        String sql=" DELETE EXE_CONDITION_IMPORT WHERE IMP_ID=#{IMP_ID} AND TO_CHAR(LAST_UPDATED_STAMP, 'YYYYMMDD.HH24MISS')||'.000'=#{LAST_UPDATED_STAMP_OLD} ";
        if(session.getForUpdate().update(sql, param)==0){
            throw new Exception("当前数据已经改变，请重新操作！");
        };
        sql=" DELETE "+impTab+" WHERE IMP_ID=#{IMP_ID} ";
        session.getForUpdate().update(sql, param);
    }

    public JSONObject queryImport(JSONObject input) throws Exception {
        String sql="SELECT * FROM EXE_CONDITION_IMPORT WHERE 1=1 ";
        final JSONObject[] result = {null};
        session.getForResultSet().sql(sql, input, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                if(rs.next()){
                    JSONObject json=rs.toRowJson();
                    String cid=json.getString("CONDITION_ID");
                    String mid=json.getString("DIMENSION_ID");
                    Condition con= ConditionUtil.getCondition(cid, session);
                    String tabName=con.getTableKey().getTabName(mid);
                    JSONObject inp=new JSONObject();
                    inp.put("IMP_ID", json.getString("IMP_ID"));
                    String sql=" SELECT * FROM "+tabName+"_EXEC_I A WHERE 1=1 AND A.IMP_STATE='0' ";
                    json.put("ITEM_S", session.getForJSON().query(sql, inp));
                    sql=" SELECT * FROM "+tabName+"_EXEC_I A WHERE 1=1 AND A.IMP_STATE>'0' ";
                    json.put("ITEM_F", session.getForJSON().query(sql, inp));
                    json.put("START_DATE", DateUtil.dateTimeToStr(json.getDate("START_DATE")));
                    json.put("END_DATE", DateUtil.dateTimeToStr(json.getDate("END_DATE")));
                    result[0] =json;
                }
            }
        });
        return result[0];
    }

    public void importSave(String mid, JSONObject input, String personId) throws Exception {
        ConSave conSave=new ConSave(condition);
        conSave.importSave(mid, input, personId);
    }

}
